/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package BLL;

import DAL.ConnectUnit;
import DTO.Customer_DTO;
import DTO.SavingBook_DTO;
import DTO.SavingType_DTO;
import java.math.BigInteger;
import java.sql.CallableStatement;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.HashSet;

/**
 *
 * @author Nguyen Phi Hung
 */
public class SavingBook_BLL {
    
    ConnectUnit connection;
    
    public SavingBook_BLL(){
        this.connection = DAL.DAL.getDAL();
    }
    //ham lay du lieu tu 1 cau truy van
    public ResultSet ExcuteQuery(String procedure,String sql) throws SQLException, Exception
    {
        CallableStatement cs=this.connection.GetCallableStatement(procedure);
        cs.setString(1, sql);
        return cs.executeQuery();
    }
    //insert
    public boolean Insert_Update_Delete(String procedure,Customer_DTO info_customer,SavingBook_DTO info_savingbook, SavingType_DTO info_savingtype) throws Exception
    {
        CallableStatement cs=connection.GetCallableStatement(procedure);
        cs.setString(1, info_customer.getFullName());
        cs.setString(2, info_customer.getIDNumber());
        cs.setString(3, info_savingtype.getName());
        cs.setInt(4, info_savingtype.getTerm());
        cs.setDate(5,info_savingbook.getOpeningDate());
        cs.setFloat(6, info_savingbook.getAccountBalance());
        return cs.execute();
        //return conn.Insert("Customer", customer);
    }
    //delete
    /*
     * hàm sẽ trả về true nếu thông tin khách hàng đã tồn tại trên hệ thống,
     * ngược lại thì trả về fasle
     */
    public boolean KiemTraThongTin_KH(String procedure,String HoTen,String CMND) throws Exception
    {
        CallableStatement cs=this.connection.GetCallableStatement(procedure);
        cs.setString(1,HoTen);
        cs.setString(2,CMND);
        cs.registerOutParameter(3, java.sql.Types.BOOLEAN);
        cs.execute();
        boolean result=cs.getBoolean(3);
        if(result==true)
            return true;
        else
            return false;
        
    }
    //kiem tra so luong so tiet kiem
    public int SoLuong_STK(String procedure,String HoTen,String CMND) throws SQLException, Exception
    {
        CallableStatement cs=this.connection.GetCallableStatement(procedure);
        cs.setString(1,HoTen);
        cs.setString(2, CMND);
        cs.registerOutParameter(3,java.sql.Types.INTEGER);
        cs.execute();
        return cs.getInt(3);
    }
    //kiem tra so tien gui co duoc phep gui hay khong
    /*
     * ham tra ve false neu so tien gui <so tien gui toi thieu,
     * nguoc lai thi tra ve true
     */
    public boolean KiemTraSoTienGui(String procedure,float SoTienGui) throws Exception
    {
        CallableStatement cs=this.connection.GetCallableStatement(procedure);
        cs.setFloat(1, SoTienGui);
        cs.registerOutParameter(2, java.sql.Types.BOOLEAN);
        cs.execute();
        return cs.getBoolean(2);
    }
    //ham tim kiem
    public String SearchSavingBook(
            boolean bMaSo,String MaSo,
            boolean bHoTen,String HoTen,
            boolean bCMND,String CMND,
            boolean bLoaiSo, String LoaiSo
            )
    {
       boolean isFirst=true;
       String sql = "select BookID,FullName,IDNumber,TypeName,Term,AccountBalance,OpeningDate,SBStatus\n"
            + "from (SAVINGBOOK inner join CUSTOMER on CUSTOMER.CustomerID=SAVINGBOOK.CustomerID) inner join SAVINGTYPE on SAVINGBOOK.TypeID=SAVINGTYPE.TypeID ";
       if(bMaSo)
        {
            sql+="where BookID like '%"+MaSo+"%' ";
            isFirst=false;
        }
        //
        if(bHoTen)
        {
             if (isFirst)
                {
                    sql += "where FullName like '%" + HoTen + "%' ";
                    isFirst = false;
                }
             else
             {
                 sql+="and FullName like '%" + HoTen + "%' ";
             }
        }
        //
        if(bCMND)
        {
             if (isFirst)
                {
                    sql += "where IDNumber like '%" + CMND + "%' ";
                    isFirst = false;
                }
             else
             {
                 sql+="and IDNumber like '%" + CMND + "%' ";
             }
        }
        //loai so
        if(bLoaiSo)
        {
            if(isFirst)
            {
                sql+="where TypeName like '%" + LoaiSo + "%' ";
                isFirst=false;
            }
            else
            {
                sql+="and TypeName like '%" + LoaiSo + "%' ";
            }
        }
        return sql;
    }
    public static void main(String[] str) throws Exception
    {
        Customer_DTO info_customer=new Customer_DTO();
        SavingBook_DTO info_savingbook=new SavingBook_DTO();
        SavingType_DTO info_savingtype=new SavingType_DTO();
        info_customer.setFullName("sa");
        info_customer.setIDNumber("123");
        info_savingtype.setName("sa");
        info_savingtype.setTerm(1);
        info_savingbook.setAccountBalance(1222);
        info_savingbook.setOpeningDate(null);
        SavingBook_BLL savingbook_bll=new SavingBook_BLL();
        //System.out.println( savingbook_bll.SearchSavingBook(true, "TS00000001", true, "Do Ngoc Sam", true, "1234567890",true,"Đóng"));
    }
}
